//************************************************************************************************************
// CoNatural.Data.dll
// Author: Roger Torres
//************************************************************************************************************
// You can use, modify, and distribute the source code and executable programs based on the source code.
// This source code is provided "as is" and without warranties as to performance or merchantability. 
// The author and/or distributors of this source code may have made statements about this source code. 
// Any such statements do not constitute warranties and shall not be relied on by the user in deciding 
// whether to use this source code. This source code is provided without any express or implied warranties 
// whatsoever. Because of the diversity of conditions and hardware under which this source code may be used, 
// no warranty of fitness for a particular purpose is offered. The user is advised to test the source code 
// thoroughly before relying on it. The user must assume the entire risk of using the source code.
//************************************************************************************************************

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Reflection;
using System.Linq;
using CoNatural.Data.Helpers;

namespace CoNatural.Data.SqlClient {
	public class SqlClientDbProvider : IDbProvider {
		/// <summary>
		/// Creates new SqlServer db provider.
		/// </summary>
		public SqlClientDbProvider() { }

		#region IDbProvider Members

		public IDbConnection CreateConnection() {
			return new System.Data.SqlClient.SqlConnection();
		}

		public IDbCommand CreateCommand() {
			return new System.Data.SqlClient.SqlCommand();
		}

		public Parameter CreateParameter(string name, Type type, ParameterAttribute parameterAttribute) {
			return new SqlClientParameter(name, type, parameterAttribute);
		}

		public IDbDataParameter CreateDataParameter(string name, DbType dbType, ParameterDirection direction, int size, byte precision, byte scale, bool isNullable, string udtName, object value) {
			SqlParameter p = new SqlParameter();
			p.ParameterName = name;
			if (udtName == null)
				p.DbType = dbType;
			else {
				p.SqlDbType = SqlDbType.Udt;
				p.UdtTypeName = udtName;
			}
			p.Direction = direction;
			p.Size = size;
			if (precision > 0) {
				p.Precision = precision;
				p.Scale = scale;
			}
			p.IsNullable = isNullable;
			Parameter.SetValue(p, value);
			return p;
		}

		public virtual string ScriptName(ICommand command) {
			string cmdName = command.GetType().FullName.Replace(".", "_");
			NamespaceAttribute nsAttr = command.GetType().GetCustomAttributes(typeof(NamespaceAttribute), false).FirstOrDefault() as NamespaceAttribute;
			string schemaName = nsAttr == null ? "dbo" : nsAttr.Namespace;
			return string.Concat("[", schemaName, "].[",  cmdName, "]");
		}

		public virtual string ScriptBatch(BatchExecutionContext batchCtx) {
			var sb = new StringBuilder();
			bool hasOut = false;
			BatchExecutionContext.BatchItem reusableItem = null;

			foreach (var item in batchCtx.GetItems()) {
				// append execute script
				ScriptExecute(sb, item, batchCtx.ReuseParameters);

				// cache parameters when reusing
				if(batchCtx.ReuseParameters)
					reusableItem = item;

				// save output parameters in results table
				foreach (var p in item.Parameters)
					if (p.Direction != ParameterDirection.Input) {
						hasOut = true;
						sb.AppendLine(string.Format("insert into @output values({0}, '{1}', {2})", item.Index, p.Name, ScriptParameterName(p)));
					}
			}

			// finalize script
			if (hasOut) {
				var finalSb = new StringBuilder();
				if (reusableItem != null) {
					// declare reusable parameters only once
					foreach (var p in reusableItem.Parameters)
						if (p.Direction != ParameterDirection.Input)
							ScriptParameterDeclaration(finalSb, p);
				}
				ScriptBatchHeader(finalSb);
				finalSb.Append(sb);
				ScriptBatchFooter(finalSb);
				return finalSb.ToString();
			}

			// no output parameters, just return execute scripts
			return sb.ToString();
		}

		#endregion

		#region Helpers

		private void ScriptBatchHeader(StringBuilder sb) {
			sb.AppendLine("-- declare table to record output parameter values");
			sb.AppendLine("declare @output table(commandIndex int, parameterName varchar(100), parameterValue sql_variant)");
			sb.AppendLine("----------------------------------------------------------------");
		}

		public void ScriptBatchFooter(StringBuilder sb) {
			sb.AppendLine("----------------------------------------------------------------");
			sb.AppendLine("-- return output parameters");
			sb.AppendLine("select commandIndex, parameterName, parameterValue from @output");
		}

		private void ScriptExecute(StringBuilder sb, BatchExecutionContext.BatchItem item, bool reuseParameters) {
			var declarations = new StringBuilder();
			var parameters = new StringBuilder();

			item.Context.OpenCommand();
			foreach (var p in item.Parameters) {
				p.BatchIndex = reuseParameters ? -1 : item.Index;
				if (parameters.Length > 0)
					parameters.Append(", ");
				if (p.Direction == ParameterDirection.Input)
					parameters.Append(ScriptParameterValue(p));
				else {
					if (!reuseParameters) {
						// append parameter declarations here if not reusing
						ScriptParameterDeclaration(declarations, p);
					}
					parameters.Append(ScriptParameterName(p)).Append(" OUTPUT");
				}
			}

			sb.Append(declarations);
			sb.Append("EXEC ").Append(ScriptName(item.Context.Command)).Append(" ");
			sb.Append(parameters);
			sb.AppendLine(";");
		}

		private void ScriptParameterDeclaration(StringBuilder sb, Parameter p) {
			SqlParameter sqlParameter = (SqlParameter)CreateDataParameter(
				p.Name,
				p.DbType,
				p.Direction,
				p.Size,
				p.Precision,
				p.Scale,
				p.IsNullable,
				p.UdtName,
				null
			);
			string name = ScriptParameterName(p);

			sb.Append("declare ").Append(name).Append(" ");
			sb.AppendLine(sqlParameter.SqlDbType.ToString());

			// initialize input-output parameters
			if (p.Direction == ParameterDirection.InputOutput) {
				sb.Append("set ").Append(name).Append(" = ");
				sb.AppendLine(ScriptParameterValue(p));
			}
		}

		private string ScriptParameterName(Parameter parameter) {
			if (parameter.BatchIndex >= 0)
				return "@" + parameter.Name + parameter.BatchIndex;
			else
				return "@" + parameter.Name;
		}

		private string ScriptParameterValue(Parameter parameter) {
			// if parameter is linked in batch, use name instead of value
			if (parameter.BatchLink != null)
				return ScriptParameterName(parameter.BatchLink);

			// get value from command
			object value = parameter.Value;
			if (value == DBNull.Value || value == null) {
				if (parameter.IsNullable)
					return "null";
				throw new Exception(string.Format("Not nullable command parameter {0} with null value.", parameter.Name));
			}

			switch (parameter.DbType) {
				case DbType.Boolean:
					return FormatBoolean((bool)value);

				case DbType.DateTime:
					return FormatDateTime((DateTime)value);

				case DbType.Guid:
					return FormatString(value.ToString());

				case DbType.String:
					return FormatString(value.ToString());

				default:
					return value.ToString();
			}
		}

		// Formatting helpers
		private string FormatString(string value) { return string.Format("'{0}'", value.Replace("'", "''")); }
		private string FormatDate(DateTime value) { return string.Format("'{0:0000}{1:00}{2:00}'", value.Year, value.Month, value.Day); }
		private string FormatTime(DateTime value) { return string.Format("'{0:00}:{1:00}'", value.Hour, value.Minute); }
		private string FormatDateTime(DateTime value) { return string.Format("'{0:0000}{1:00}{2:00} {3:00}:{4:00}'", value.Year, value.Month, value.Day, value.Hour, value.Minute); }
		private string FormatBoolean(bool value) { return value ? "1" : "0"; }

		#endregion Helpers
	}
}


